Days of the Week, Bitwise Edition

I recently had to help support synchronization and distribution of workloads between multiple servers. Some of this work involves Task Scheduler and, be honest, who knew Task Scheduler was this complicated? On different servers, we wanted jobs to trigger on different days of the week. The way that Task Scheduler handles this programmatically is through a numeric property called WeeklyTrigger.DaysOfWeek, where the following coefficients are packed together into a single value:

1 = Sunday

2 = Monday

4 = Tuesday

8 = Wednesday

16 = Thursday

32 = Friday

64 = Saturday

For example, 62 would be derived from 2 + 4 + 8 + 16 + 32, which means the schedule is for every weekday (excluding Saturday and Sunday), while 65 would come from 64 + 1, meaning only Saturday and Sunday. You can visualize this as follows, with a couple more examples:

You can read more about this specific enum as it relates to Task Scheduler here and here.

I’m not a big fan of “hiding” values in a single integer and requiring bitwise operations to extract them. It seems an optimization holdover from the days of punch-cards and floppy disks, where space was way more valuable than logic and self-documentation. I’ve written about it before in When the DRY principle doesn’t apply : BITWISE operations in SQL Server. Still, in 2024, we can’t escape it. I don’t have to deal with bitwise in SQL Server very often, but I recently came across…

This use case

To interface between Task Scheduler and SQL Server, I wanted a way to translate the enum to produce different output depending on the consumer. When I know the trigger value from Task Scheduler but need to expose it to something in (or from) SQL Server, I wanted a table-valued function to return the day(s) of the week given any specific enum value. I might want one of the following output styles, say, from an enum value of 3 (which means Sunday and Monday):

The first thing I had to do was define the enum in T-SQL somehow. One way is a table value constructor:

SELECT enum, dayname, dayofweek FROM

(

VALUES (1, 'Sunday', 1),

(2, 'Monday', 2),

(4, 'Tuesday', 3),

(8, 'Wednesday', 4),

(16, 'Thursday', 5),

(32, 'Friday', 6),

(64, 'Saturday', 7)

) AS denum (enum, dayname, dayofweek);

The output of this in isolation should not be shocking:

If I know the DaysOfWeek value, I can pull the relevant values from this constructor by simply ANDing the coefficient with the value passed in:

DECLARE @Enum tinyint = 42;

SELECT dayname FROM

(

VALUES (1, 'Sunday', 1),

(2, 'Monday', 2),

(4, 'Tuesday', 3),

(8, 'Wednesday', 4),

(16, 'Thursday', 5),

(32, 'Friday', 6),

(64, 'Saturday', 7)

) AS denum (enum, dayname, dayofweek)

WHERE enum & @Enum > 0;

Output:

Now I know how I can put this in a function:

CREATE OR ALTER FUNCTION dbo.GetDaysOfWeekFromEnum

(

@Enum tinyint

)

RETURNS TABLE

RETURN (

WITH cte AS

(

SELECT enum, dayname, dayofweek FROM

(

VALUES (1, 'Sunday', 1),

(2, 'Monday', 2),

(4, 'Tuesday', 3),

(8, 'Wednesday', 4),

(16, 'Thursday', 5),

(32, 'Friday', 6),

(64, 'Saturday', 7)

) AS denum (enum, dayname, dayofweek)

WHERE enum & @Enum > 0

)

SELECT dayname FROM cte

);

If I call it with various arguments:

SELECT dayname FROM dbo.GetDaysOfWeekFromEnum(62);

SELECT dayname FROM dbo.GetDaysOfWeekFromEnum(65);

SELECT dayname FROM dbo.GetDaysOfWeekFromEnum(34);

SELECT dayname FROM dbo.GetDaysOfWeekFromEnum(42);

I get the same output illustrated above:

This handles the “set” style of output, where each day is on its own row. In order to derive the different styles, I can add an argument and then create a union between the different possibilities:

CREATE OR ALTER FUNCTION dbo.GetDaysOfWeekFromEnum

(

@Enum tinyint,

@Style varchar(4) /* set | csv | json */

)

RETURNS TABLE

RETURN (

WITH cte AS

(

SELECT enum, dayname, dayofweek FROM

(

VALUES (1, 'Sunday', 1),

(2, 'Monday', 2),

(4, 'Tuesday', 3),

(8, 'Wednesday', 4),

(16, 'Thursday', 5),

(32, 'Friday', 6),

(64, 'Saturday', 7)

) AS denum (enum, dayname, dayofweek)

WHERE enum & @Enum > 0

)

SELECT dayname FROM

(

SELECT dayname

FROM cte

WHERE @Style = 'set'

UNION ALL

SELECT STRING_AGG(dayname, ',')

WITHIN GROUP (ORDER BY dayofweek)

FROM cte

WHERE @Style = 'csv'

UNION ALL

SELECT Day FROM

(

SELECT dayname FROM

(

SELECT dayname

FROM cte

WHERE @Style = 'json'

) AS i

ORDER BY dayofweek

FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

) AS o(Day)

) AS f WHERE dayname IS NOT NULL

);

If I ask for the days for 62 in each style:

SELECT dayname FROM dbo.GetDaysOfWeekFromEnum(62, 'set');

SELECT dayname FROM dbo.GetDaysOfWeekFromEnum(62, 'csv');

SELECT dayname FROM dbo.GetDaysOfWeekFromEnum(62, 'json');

You can see the different output:

Now all of my PowerShell and C# can just pass the trigger value into SQL Server and not have to think about it. This works great for me, because T-SQL is where I am much more comfortable.

Note that there’s not a tidy way in this function to ensure that the set version will return the days in proper order, unless you output the dayofweek column (and a dummy column for the other two union queries).

A second use case

Sometimes I need to know all the dates a job will run within a date range (say, a month). For this I wanted a different TVF that returns a list of dates, on the days of the week represented by the enum, between @StartDate and @EndDate. What this really means is, generate a series of dates from the start date to the end date, and from that series, return the dates that match any of the weekdays present in the DaysOfWeek value.

In SQL Server 2022, we can use GENERATE_SERIES^* to create a set of days. For example, to get all the dates in December 2024:

DECLARE @StartDate date = '20241201',

@EndDate date = '20241231';

SELECT DATEADD(DAY, value, @StartDate)

FROM GENERATE_SERIES(0, DATEDIFF(DAY, @StartDate, @EndDate));

I’m not going to prove it with a screenshot, but that returns a set of 31 days, from December 1st through the 31st.

Next, to match these dates up with the days of the week present in the enum, I can extract the DATENAME for the day of week from each date, and join on that. For example:

CREATE OR ALTER FUNCTION dbo.GetDatesFromEnumInRange

(

@Enum tinyint,

@StartDate date,

@EndDate date

)

RETURNS TABLE

RETURN

(

WITH daysofweek AS

(

SELECT dayname FROM

(

VALUES (1, 'Sunday', 1),

(2, 'Monday', 2),

(4, 'Tuesday', 3),

(8, 'Wednesday', 4),

(16, 'Thursday', 5),

(32, 'Friday', 6),

(64, 'Saturday', 7)

) AS denum (enum, dayname, dayofweek)

WHERE enum & @Enum > 0

days(Date) AS

(

SELECT DATEADD(DAY, value, @StartDate)

FROM GENERATE_SERIES(0, DATEDIFF(DAY, @StartDate, @EndDate))

)

SELECT days.Date,

daysofweek.dayname

FROM days

INNER JOIN daysofweek

ON DATENAME(WEEKDAY, days.Date) = daysofweek.dayname

);

^* Note: I have to rely on either @@LANGUAGE being some form of English, or use the dayofweek value and rely on @@DATEFIRST. If you use a different language, or a different DATEFIRST setting, you could just construct your enum accordingly. Just remember that any user could have a different setting in their own session.

Also, GENERATE_SERIES requires SQL Server 2022 or Azure SQL Database; see alternatives for earlier versions in this post.

If I call that for the enum 62, for the month of December:

DECLARE @Enum tinyint = 62,

@StartDate date = '20241201',

@EndDate date = '20241231';

SELECT Date, dayname

FROM dbo.GetDatesFromEnumInRange

(@Enum, @StartDate, @EndDate)

ORDER BY Date;

Here is the output:

Future enhancements?

There are definitely some fixes for the future. One is that, now that I have two separate functions referencing it, I should be storing my enum in a table somewhere. The other is that I’d like to fix it so that I don’t have to rely on a stable DATEFIRST setting but, thankfully, the function rarely gets invoked manually.

Depending on your use case, you may also want to integrate a calendar table, so that you can highlight (or even exclude) holidays or scheduled maintenance windows from certain results.

Register for Simple Talk